package com.demo.dao.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.apache.commons.dbutils.BasicRowProcessor;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.RowProcessor;

import com.demo.bean.Category;
import com.demo.dao.ICategoryDao;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.springframework.beans.factory.annotation.Autowired;

public class CategoryDaoImpl implements ICategoryDao {

	@Autowired
	private final DataSource dataSource = new ComboPooledDataSource("remote");
	
	@Override
	public List<Category> page(int index, int limit) throws SQLException {
		List<Category> ret = new ArrayList<>();
		int offset = limit * index;
		String sql = String.format("SELECT * FROM category LIMIT %d, %d", offset, limit);
		try(Connection conn = dataSource.getConnection(); Statement stmt = conn.createStatement()) {
			RowProcessor rowp = new BasicRowProcessor();
			ret = rowp.toBeanList(stmt.executeQuery(sql), Category.class);
		}
		return ret;
	}

	@Override
	public boolean update(Category category) throws SQLException {
		QueryRunner query = new QueryRunner(dataSource);
		String sql = "UPDATE category SET c_name = ?, place = ?, createTime = ?, type = ? WHERE c_id = ?";
		System.out.println("update data: " + category);

		query.update(sql,
				category.getC_name(),
				category.getPlace(),
				category.getCreateTime(),
				category.getType(),
				category.getC_id());
		return true;
	}

	@Override
	public boolean delete(int id) throws SQLException {
		QueryRunner query = new QueryRunner(dataSource);
		String sql = "DELETE FROM category WHERE c_id = ?";
		query.update(sql, id);
		return true;
	}

	@Override
	public Integer count(){
		try (Connection conn = dataSource.getConnection(); Statement stmt = conn.createStatement()) {
			String sql = "SELECT COUNT(*) AS total FROM category";
			ResultSet rs = stmt.executeQuery(sql);
			rs.next();
			return rs.getInt("total");
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}

	@Override
	public boolean add(Category category) throws SQLException {
		QueryRunner query = new QueryRunner(dataSource);
		String sql = "INSERT INTO category (c_name, place, createTime, type) VALUES (?, ?, ?, ?)";
		System.out.println("add data: " + category);
		
		query.update(sql, 
				category.getC_name(),
				category.getPlace(),
				category.getCreateTime(),
				category.getType());
		return true;
	}

}
